package com.imut.lagain.repository;
import org.apache.ibatis.annotations.Select;
import org.slf4j.LoggerFactory;

import com.imut.lagain.entity.SensoryExercise;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.springframework.data.jpa.repository.Modifying;

import java.time.LocalDateTime;
import java.util.List;
import java.util.Optional;

/**
 * 感官练习数据访问层
 */
@Mapper
public interface SensoryExerciseRepository extends BaseMapper<SensoryExercise> {

    /**
     * 根据用户ID查询感官练习记录
     */
    @Select("SELECT s FROM SensoryExercise s WHERE s.userId = :userId AND s.isDeleted = false ORDER BY s.createdAt DESC")
    List<SensoryExercise> findByUserId(@Param("userId") Long userId);

    /**
     * 根据用户ID和会话ID查询感官练习
     */
    @Select("SELECT s FROM SensoryExercise s WHERE s.userId = :userId AND s.sessionId = :sessionId AND s.isDeleted = false ORDER BY s.createdAt DESC")
    List<SensoryExercise> findByUserIdAndSessionId(@Param("userId") Long userId, @Param("sessionId") Long sessionId);

    /**
     * 根据用户ID和练习类型查询
     */
    @Select("SELECT s FROM SensoryExercise s WHERE s.userId = :userId AND s.exerciseType = :exerciseType AND s.isDeleted = false ORDER BY s.createdAt DESC")
    List<SensoryExercise> findByUserIdAndExerciseType(@Param("userId") Long userId, @Param("exerciseType") String exerciseType);

    /**
     * 根据用户ID和状态查询
     */
    @Select("SELECT s FROM SensoryExercise s WHERE s.userId = :userId AND s.status = :status AND s.isDeleted = false ORDER BY s.createdAt DESC")
    List<SensoryExercise> findByUserIdAndStatus(@Param("userId") Long userId, @Param("status") String status);

    /**
     * 根据用户ID和时间范围查询
     */
    @Select("SELECT s FROM SensoryExercise s WHERE s.userId = :userId AND s.createdAt BETWEEN :startDate AND :endDate AND s.isDeleted = false ORDER BY s.createdAt DESC")
    List<SensoryExercise> findByUserIdAndDateRange(@Param("userId") Long userId, 
                                                   @Param("startDate") LocalDateTime startDate, 
                                                   @Param("endDate") LocalDateTime endDate);

    /**
     * 根据练习ID查询
     */
    @Select("SELECT s FROM SensoryExercise s WHERE s.id = :exerciseId AND s.isDeleted = false")
    Optional<SensoryExercise> findByExerciseId(@Param("exerciseId") Long exerciseId);

    /**
     * 更新练习进度
     */
    @Modifying
    @Select("UPDATE SensoryExercise s SET s.completionRate = :completionRate, s.status = :status, s.updatedAt = :updatedAt WHERE s.id = :exerciseId")
    int updateProgress(@Param("exerciseId") Long exerciseId, 
                      @Param("completionRate") Double completionRate, 
                      @Param("status") String status, 
                      @Param("updatedAt") LocalDateTime updatedAt);

    /**
     * 完成练习
     */
    @Modifying
    @Select("UPDATE SensoryExercise s SET s.status = 'completed', s.completedAt = :completedAt, s.anxietyAfter = :anxietyAfter, s.groundingScore = :groundingScore, s.updatedAt = :updatedAt WHERE s.id = :exerciseId")
    int completeExercise(@Param("exerciseId") Long exerciseId, 
                        @Param("completedAt") LocalDateTime completedAt, 
                        @Param("anxietyAfter") Integer anxietyAfter, 
                        @Param("groundingScore") Integer groundingScore, 
                        @Param("updatedAt") LocalDateTime updatedAt);

    /**
     * 更新感官项目
     */
    @Modifying
    @Select("UPDATE SensoryExercise s SET s.seeItems = :seeItems, s.touchItems = :touchItems, s.hearItems = :hearItems, s.smellItems = :smellItems, s.tasteItems = :tasteItems, s.updatedAt = :updatedAt WHERE s.id = :exerciseId")
    int updateSensoryItems(@Param("exerciseId") Long exerciseId, 
                          @Param("seeItems") String seeItems, 
                          @Param("touchItems") String touchItems, 
                          @Param("hearItems") String hearItems, 
                          @Param("smellItems") String smellItems, 
                          @Param("tasteItems") String tasteItems, 
                          @Param("updatedAt") LocalDateTime updatedAt);

    /**
     * 统计用户练习次数
     */
    @Select("SELECT COUNT(s) FROM SensoryExercise s WHERE s.userId = :userId AND s.isDeleted = false")
    Long countByUserId(@Param("userId") Long userId);

    /**
     * 统计用户完成的练习次数
     */
    @Select("SELECT COUNT(s) FROM SensoryExercise s WHERE s.userId = :userId AND s.status = 'completed' AND s.isDeleted = false")
    Long countCompletedByUserId(@Param("userId") Long userId);

    /**
     * 统计用户各类型练习次数
     */
    @Select("SELECT s.exerciseType, COUNT(s) FROM SensoryExercise s WHERE s.userId = :userId AND s.status = 'completed' AND s.isDeleted = false GROUP BY s.exerciseType")
    List<Object[]> countByUserIdAndExerciseType(@Param("userId") Long userId);

    /**
     * 查询用户平均练习时长
     */
    @Select("SELECT AVG(s.duration) FROM SensoryExercise s WHERE s.userId = :userId AND s.status = 'completed' AND s.isDeleted = false")
    Double getAverageDurationByUserId(@Param("userId") Long userId);

    /**
     * 查询用户平均接地效果评分
     */
    @Select("SELECT AVG(s.groundingScore) FROM SensoryExercise s WHERE s.userId = :userId AND s.groundingScore IS NOT NULL AND s.isDeleted = false")
    Double getAverageGroundingScore(@Param("userId") Long userId);

    /**
     * 查询用户平均焦虑改善程度
     */
    @Select("SELECT AVG(s.anxietyBefore - s.anxietyAfter) FROM SensoryExercise s WHERE s.userId = :userId AND s.anxietyBefore IS NOT NULL AND s.anxietyAfter IS NOT NULL AND s.isDeleted = false")
    Double getAverageAnxietyImprovement(@Param("userId") Long userId);

    /**
     * 查询用户平均完成率
     */
    @Select("SELECT AVG(s.completionRate) FROM SensoryExercise s WHERE s.userId = :userId AND s.completionRate IS NOT NULL AND s.isDeleted = false")
    Double getAverageCompletionRate(@Param("userId") Long userId);

    /**
     * 查询用户最近的练习记录
     */
    @Select("SELECT s FROM SensoryExercise s WHERE s.userId = :userId AND s.isDeleted = false ORDER BY s.createdAt DESC")
    List<SensoryExercise> findRecentExercisesByUserId(@Param("userId") Long userId, org.springframework.data.domain.Pageable pageable);

    /**
     * 查询用户最喜欢的练习类型
     */
    @Select("SELECT s.exerciseType FROM SensoryExercise s WHERE s.userId = :userId AND s.groundingScore >= 7 AND s.isDeleted = false GROUP BY s.exerciseType ORDER BY COUNT(s) DESC")
    List<String> getPreferredExerciseTypes(@Param("userId") Long userId);

    /**
     * 查询用户最有效的感官类型
     */
    @Select("SELECT s.focusSense FROM SensoryExercise s WHERE s.userId = :userId AND s.groundingScore >= 7 AND s.focusSense IS NOT NULL AND s.isDeleted = false GROUP BY s.focusSense ORDER BY COUNT(s) DESC")
    List<String> getMostEffectiveSenses(@Param("userId") Long userId);

    /**
     * 查询未完成的练习
     */
    @Select("SELECT s FROM SensoryExercise s WHERE s.userId = :userId AND s.status IN ('pending', 'in_progress') AND s.isDeleted = false ORDER BY s.createdAt DESC")
    List<SensoryExercise> findIncompleteExercises(@Param("userId") Long userId);

    /**
     * 查询超时未完成的练习
     */
    @Select("SELECT s FROM SensoryExercise s WHERE s.status = 'in_progress' AND s.startedAt < :timeoutThreshold AND s.isDeleted = false")
    List<SensoryExercise> findTimeoutExercises(@Param("timeoutThreshold") LocalDateTime timeoutThreshold);

    /**
     * 查询用户在特定环境下的练习效果
     */
    @Select("SELECT s.environment, AVG(s.groundingScore) FROM SensoryExercise s WHERE s.userId = :userId AND s.environment IS NOT NULL AND s.groundingScore IS NOT NULL AND s.isDeleted = false GROUP BY s.environment")
    List<Object[]> getEffectivenessByEnvironment(@Param("userId") Long userId);
}
